
-- PRABHANJAN (19 MAY 2004) TO TRANSFER LEGACY CONTACT PERSON DATA FROM ADDRESS TABLE TO SALES_ORDER TABLE.
-- PREVIOUSLY CONTACT_PERSONS SELECTED FROM SALES ORDER WERE STORED IN ADDRESS TABLE ONLY.
-- NOW THEY WILL BE STORED IN SALES_ORDER (SO_BILLING_CONTACT_PERSON, SO_SHIPPING_CONTACT_PERSON).

CREATE PROCEDURE sp_copy_ct_persons_from_address_to_sales_order

AS
BEGIN
	DECLARE @BILLING_CT_PERSON VARCHAR(50)
	DECLARE @SHIPPING_CT_PERSON VARCHAR(50)
	DECLARE @BILLING_ADDRESS_ID INT
	DECLARE @SHIPPING_ADDRESS_ID INT
	DECLARE @SALESORDER_ID INT

	DECLARE SALESORDER_CURSOR CURSOR FOR SELECT SALESORDER_ID, SO_BILLING_ADDRESS_ID, SO_SHIPPING_ADDRESS_ID FROM SALES_ORDER
	OPEN SALESORDER_CURSOR
	FETCH NEXT FROM SALESORDER_CURSOR INTO @SALESORDER_ID, @BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @BILLING_CT_PERSON = CONTACT_PERSON FROM ADDRESS WHERE ADDRESS_ID = @BILLING_ADDRESS_ID
		SELECT @SHIPPING_CT_PERSON = CONTACT_PERSON FROM ADDRESS WHERE ADDRESS_ID = @SHIPPING_ADDRESS_ID
		UPDATE SALES_ORDER SET SO_BILLING_CONTACT_PERSON = @BILLING_CT_PERSON, SO_SHIPPING_CONTACT_PERSON = @SHIPPING_CT_PERSON WHERE SALESORDER_ID = @SALESORDER_ID
		UPDATE CHANGE_SALES_ORDER SET CSO_BILLING_CONTACT_PERSON = @BILLING_CT_PERSON, CSO_SHIPPING_CONTACT_PERSON = @SHIPPING_CT_PERSON WHERE SALESORDER_ID = @SALESORDER_ID
		FETCH NEXT FROM SALESORDER_CURSOR INTO @SALESORDER_ID, @BILLING_ADDRESS_ID, @SHIPPING_ADDRESS_ID	
			
	END
	CLOSE SALESORDER_CURSOR
	DEALLOCATE SALESORDER_CURSOR

END